package com.munichosica.myapp.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.munichosica.myapp.dao.UbigeoDao;
import com.munichosica.myapp.dto.Ubigeo;
import com.munichosica.myapp.exceptions.UbigeoDaoException;

public class UbigeoDaoImpl implements UbigeoDao{

	private StringBuilder sql=null;
	public UbigeoDaoImpl() {
		sql=new StringBuilder();
		sql.append("SELECT UBICODIGO_V AS CODIGO," +
				"UBINOMBRE_V AS UBIGEO " +
				"FROM UBIGEO ");
	}
	
	@Override
	public List<Ubigeo> listarDepartamento() throws UbigeoDaoException {
		sql.append("WHERE LENGTH(TRIM(UBICODIGO_V))=2 ORDER BY UBINOMBRE_V ASC");
		List<Ubigeo> list=null;
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		try {
			conn=ResourceManager.getConnection();
			stmt=conn.prepareStatement(sql.toString());
			boolean results=stmt.execute();
			if(results){
				list=new ArrayList<Ubigeo>();
				rs=stmt.getResultSet();
				Ubigeo ubigeo=null;
				while(rs.next()){
					ubigeo=new Ubigeo();
					ubigeo.setUbicodigoV(rs.getString("CODIGO"));
					ubigeo.setUbinombreV(rs.getString("UBIGEO"));
					list.add(ubigeo);
				}
			}
		} catch (SQLException e) {
			throw new UbigeoDaoException(e.getMessage(), e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			ResourceManager.close(conn);
		}
		return list;
	}

	@Override
	public List<Ubigeo> listarProvincia(String idDepartamento)
			throws UbigeoDaoException {
		sql.append("WHERE LENGTH(TRIM(UBICODIGO_V))=4 AND UBICODIGO_V LIKE ? ORDER BY UBINOMBRE_V ASC");
		List<Ubigeo> list=null;
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		try {
			conn=ResourceManager.getConnection();
			stmt=conn.prepareStatement(sql.toString());
			stmt.setString(1, idDepartamento.trim()+'%');
			boolean results=stmt.execute();
			if(results){
				list=new ArrayList<Ubigeo>();
				rs=stmt.getResultSet();
				Ubigeo ubigeo=null;
				while(rs.next()){
					ubigeo=new Ubigeo();
					ubigeo.setUbicodigoV(rs.getString("CODIGO"));
					ubigeo.setUbinombreV(rs.getString("UBIGEO"));
					list.add(ubigeo);
				}
			}
		} catch (SQLException e) {
			throw new UbigeoDaoException(e.getMessage(), e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			ResourceManager.close(conn);
		}
		return list;
	}

	@Override
	public List<Ubigeo> listarDistrito(String idProvincia)
			throws UbigeoDaoException {
		sql.append("WHERE LENGTH(TRIM(UBICODIGO_V))=6 AND UBICODIGO_V LIKE ? ORDER BY UBINOMBRE_V ASC");
		List<Ubigeo> list=null;
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		try {
			conn=ResourceManager.getConnection();
			stmt=conn.prepareStatement(sql.toString());
			stmt.setString(1, idProvincia.trim()+'%');
			boolean results=stmt.execute();
			if(results){
				list=new ArrayList<Ubigeo>();
				rs=stmt.getResultSet();
				Ubigeo ubigeo=null;
				while(rs.next()){
					ubigeo=new Ubigeo();
					ubigeo.setUbicodigoV(rs.getString("CODIGO"));
					ubigeo.setUbinombreV(rs.getString("UBIGEO"));
					list.add(ubigeo);
				}
			}
		} catch (SQLException e) {
			throw new UbigeoDaoException(e.getMessage(), e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			ResourceManager.close(conn);
		}
		return list;
	}

}
